/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package edu.pdx.cs.guavadata;

import edu.pdx.cs.guavadata.data.Schema;
import edu.pdx.cs.guavadata.domain.Domain;
import edu.pdx.cs.guavadata.query.AddColumn;
import edu.pdx.cs.guavadata.query.AddElement;
import edu.pdx.cs.guavadata.query.AddTable;
import edu.pdx.cs.guavadata.query.AlterColumnRename;
import edu.pdx.cs.guavadata.query.AlterElementRename;
import edu.pdx.cs.guavadata.query.AlterTableRename;
import edu.pdx.cs.guavadata.query.Command;
import edu.pdx.cs.guavadata.query.DropColumn;
import edu.pdx.cs.guavadata.query.DropElement;
import edu.pdx.cs.guavadata.query.DropTable;
import edu.pdx.cs.guavadata.query.ForeignKey;
import edu.pdx.cs.guavadata.query.Operator;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.StringReader;
import java.io.StringWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;

/**
 *
 * @author VuTran
 */
public class SchemaController {
    Document xml;
    String docName;
    static String DEFAULT_DOC_NAME = "schema_log.xml";
    Document dbSchema;
    int dbVersion;
    boolean usingDBSchema = false;
    boolean logging = true;
    Channel channel;

    public SchemaController(String path, String docName, Schema sch, Channel c, Boolean logging) {
        this.logging = logging;
        init(path, docName, sch, c);
    }

    public SchemaController(String path, Schema sch) {
        init(path, null, sch, null);
    }

    public SchemaController(String path, String docName, Schema sch, Channel c) {
        init(path, docName, sch, c);
    }

    private void init(String path, String file, Schema sch, Channel c) {
        try {
            DocumentBuilderFactory dbf = null;
            DocumentBuilder db = null;
            if (logging) {
                if (file == null) {
                    this.docName = DEFAULT_DOC_NAME;
                } else {
                    this.docName = file;
                }
                channel = c;
                try {
                    dbf = DocumentBuilderFactory.newInstance();
                    db = dbf.newDocumentBuilder();
                    this.xml = db.parse(new File(this.docName));
                    updateXmlChanges(sch);
                } catch (FileNotFoundException fe) {
                    this.xml = db.newDocument();
                    Element rootElement = xml.createElement("DDLs");
                    xml.appendChild(rootElement);
                    transactionToXml(sch.getTransaction());
                    Element version = xml.createElement("version");
                    version.appendChild(xml.createTextNode("0"));
                    rootElement.appendChild(version);
                    save();
                } catch (Exception e) {
                    System.err.println("ERROR: " + e.toString());
                }
            } else {
                dbf = DocumentBuilderFactory.newInstance();
                db = dbf.newDocumentBuilder();
                this.xml = db.newDocument();
                Element rootElement = xml.createElement("DDLs");
                xml.appendChild(rootElement);
                transactionToXml(sch.getTransaction());
                Element version = xml.createElement("version");
                version.appendChild(xml.createTextNode("0"));
                rootElement.appendChild(version);
            }
        } catch (ParserConfigurationException ex) {
            System.err.println("ERROR: " + ex.toString());
        }
        
    }

    public void updateXmlChanges(Schema sch) {
        List<Command> tXml = transactionFromXmlChanges(xml.getFirstChild().getChildNodes()).getContents();
        List<Command> tMem = sch.getTransaction().getContents();
        boolean changes = false;

        // verify if we need to add new table by checking table in mem exist in xml schema?
        for (Command memCom : tMem) {
            if (memCom.root instanceof AddTable) {
                // look for table name
                AddTable at = null;
                for (Command xmlCom : tXml) {
                    if (xmlCom.root instanceof AddTable) {
                        if (((AddTable) memCom.root).getTable().equals(((AddTable) xmlCom.root).getTable())) {
                            at = (AddTable) xmlCom.root;
                            break;
                        }
                    }
                }

                if (at == null) {
                    logOperator(memCom.root);
                    changes = true;
                } else {
                    List<String> memCols = ((AddTable) memCom.root).getColumns();
                    List<String> xmlCols = at.getColumns();

                    // adjust columns through version
                    for (Command cmd : tXml) {
                        if (cmd.root instanceof AddColumn) {
                            if (((AddColumn) cmd.root).getTable().equals(at.getTable())) {
                                xmlCols.add(((AddColumn) cmd.root).getColumn());
                            }
                        }

                        if (cmd.root instanceof DropColumn) {
                            if (((DropColumn) cmd.root).getTable().equals(at.getTable())) {
                                xmlCols.remove(((DropColumn) cmd.root).getColumn());
                            }
                        }
                    }

                    for (String memCol : memCols) {     // add new columns
                        if (!xmlCols.contains(memCol)) {
                            logOperator(new AddColumn(at.getTable(), memCol,
                                    ((AddTable) memCom.root).getDomains().get(memCols.indexOf(memCol))));
                            changes = true;
                        }
                    }

                    for (String xmlCol : xmlCols) {   // remove columms
                        if (!memCols.contains(xmlCol)) {
                            logOperator(new DropColumn(at.getTable(), xmlCol));
                            changes = true;
                        }
                    }
                }
            }
        }

        for (Command xmlCom : tXml) {
            boolean tableFound = false;
            if (xmlCom.root instanceof AddTable) {
                for (Command memCom : tMem) {
                    if (memCom.root instanceof AddTable) {
                        if (((AddTable) xmlCom.root).getTable().equals(((AddTable) memCom.root).getTable())) {
                            tableFound = true;
                            break;
                        }
                    }
                }
            }

            if (!tableFound && (xmlCom.root instanceof AddTable)) {
                logOperator(new DropTable(((AddTable) xmlCom.root).getTable()));
                changes = true;
            }
        }

        if (changes) {
            NodeList versions = xml.getElementsByTagName("version");
            int newestVersion = 0;
            try {
                newestVersion = Integer.valueOf(versions.item(versions.getLength() - 1).getFirstChild().getNodeValue());
            }  catch (Exception e) {
            }

            Element version = xml.createElement("version");
            version.appendChild(xml.createTextNode(String.valueOf(++newestVersion)));
            xml.getFirstChild().appendChild(version);
            save();
        }
    }

    public void retrieveDBSchema(Schema sch) {
        try {
            usingDBSchema = true;
            DBConnection.executeNonQuery("CREATE TABLE DBVersion (Version int NOT NULL, XMLSchema text NULL, XMLChannel text NULL, CONSTRAINT pk_DBVersion PRIMARY KEY CLUSTERED (Version))");

            DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
            DocumentBuilder db = dbf.newDocumentBuilder();
            ResultSet rs = DBConnection.executeQuery("select * from DBVersion where version=(select max(version) from dbversion)");
            /* Don't understand why result get more result??
            while (rs.next()) {
                System.err.println("Next");
            }*/
            if (rs.next()) {
                dbVersion = rs.getInt("version");
                System.err.println(rs.getString("XMLSchema"));
                InputSource is = new InputSource();
                is.setCharacterStream(new StringReader(rs.getString("XMLSchema")));
                dbSchema = db.parse(is);
            } else {
                Document dbx = db.newDocument();
                Element rootElement = dbx.createElement("DDLs");
                dbx.appendChild(rootElement);
                NodeList ddlChildren = ((Element) xml.getElementsByTagName("DDLs").item(0)).getChildNodes();
                for (int i = 0; i < ddlChildren.getLength(); i++) {
                    Element item = (Element) ddlChildren.item(i);
                    if (item.getTagName().equals("version"))
                        break;
                    Node importedItem = dbx.importNode(item, true);
                    rootElement.appendChild(importedItem);
                }

                String c = "NULL";
                if (channel != null)
                    c = channel.toDocument().toString();

                // convert xml document to xml string
                StringWriter stw = new StringWriter();
                Transformer serializer = TransformerFactory.newInstance().newTransformer();
                serializer.transform(new DOMSource(dbx), new StreamResult(stw));



                DBConnection.executeNonQuery("insert into DBversion (version, xmlschema, xmlchannel) values (0, '" +
                        stw.toString() + "', '" + c + "')");
                dbVersion = 0;
                dbSchema = dbx;
            }

        } catch (Exception ex) {
            System.err.println("ERROR: " + ex.getMessage());
        }
    }

    public void transactionToXml(Transaction t) {
        if (t.getContents() == null)
            return;
        for (Command c : t.getContents())
            logOperator(c.root);
        if (logging)
            save();
    }

    public void logOperator(Operator op) {
        if (op instanceof AddTable) {
            writeDDL(createAddTableLog((AddTable) op));
        } else if (op instanceof AlterTableRename) {
            AlterTableRename rt = (AlterTableRename) op;
            Element e = xml.createElement("table");
            e.setAttribute("type", "rename");
            e.setAttribute("old", rt.getOldName());
            e.setAttribute("new", rt.getNewName());
            writeDDL(e);
        } else if (op instanceof DropTable) {
            DropTable dt = (DropTable) op;
            Element e = xml.createElement("table");
            e.setAttribute("type", "delete");
            e.setAttribute("name", dt.getTable());
            writeDDL(e);
        } else if (op instanceof AddColumn) {
            AddColumn ac = (AddColumn) op;
            Element e = xml.createElement("column");
            e.setAttribute("type", "add");
            e.setAttribute("name", ac.getColumn());
            e.setAttribute("table", ac.getTable());
            e.appendChild(ac.getDomain().ToXml(xml));
            writeDDL(e);
        } else if (op instanceof AlterColumnRename) {
            AlterColumnRename rc = (AlterColumnRename) op;
            Element e = xml.createElement("column");
            e.setAttribute("type", "rename");
            e.setAttribute("old", rc.getOldName());
            e.setAttribute("new", rc.getNewName());
            e.setAttribute("table", rc.getTable());
            writeDDL(e);
        } else if (op instanceof DropColumn) {
            DropColumn dc = (DropColumn) op;
            Element e = xml.createElement("column");
            e.setAttribute("type", "delete");
            e.setAttribute("name", dc.getColumn());
            e.setAttribute("table", dc.getTable());
            writeDDL(e);
        } else if (op instanceof AddElement) {
            AddElement ae = (AddElement) op;
            Element e = xml.createElement("element");
            e.setAttribute("type", "add");
            e.setAttribute("column", ae.getColumn());
            e.setAttribute("value", ae.toString());
            e.setAttribute("table", ae.getTable());
            writeDDL(e);
        } else if (op instanceof AlterElementRename) {
            AlterElementRename re = (AlterElementRename) op;
            Element e = xml.createElement("element");
            e.setAttribute("type", "rename");
            e.setAttribute("column", re.getColumn());
            e.setAttribute("old", re.getOldName());
            e.setAttribute("new", re.getNewName());
            e.setAttribute("table", re.getTable());
            writeDDL(e);
        } else if (op instanceof DropElement) {
            DropElement de = (DropElement) op;
            Element e = xml.createElement("element");
            e.setAttribute("type", "delete");
            e.setAttribute("column", de.getColumn());
            e.setAttribute("table", de.getTable());
            writeDDL(e);
        } else if (op instanceof ForeignKey) {
            ForeignKey fk = (ForeignKey) op;
            writeDDL(createForeignKeyLog(fk));
        }
    }

    public Element createAddTableLog(AddTable at) {
        Element ate = xml.createElement("table");
        ate.setAttribute("type", "add");
        ate.setAttribute("name", at.getTable());
        List<String> atCol = at.getColumns();
        List<String> atKey = at.getKeys();
        for (int i = 0; i < atCol.size(); i++) {
            Element ce = xml.createElement("column");
            ce.setAttribute("name", atCol.get(i));
            ce.setAttribute("key", String.valueOf(atKey.contains(atCol.get(i))));
            ce.appendChild(at.getDomains().get(i).ToXml(xml));
            ate.appendChild(ce);
        }
        return ate;
    }

    private Element createForeignKeyLog(ForeignKey fk) {
        Element fke = xml.createElement("fk");
        fke.setAttribute("type", "add");
        String childCols = "";
        String childConds = "";
        String parentCols = "";
        String parentConds = "";
        for (int i = 0; i < fk.getChildColumns().size(); i++) {
            childCols = childCols + fk.getChildColumns().get(i);
            if (i < (fk.getChildColumns().size() - 1))
                childCols = childCols + ",";
        }
        for (int i = 0; i < fk.getChildConditions().size(); i++) {
            childConds = childConds + fk.getChildConditions().get(i).toString();
            if (i < (fk.getChildConditions().size() - 1))
                childConds = childConds + ",";
        }
        for (int i = 0; i < fk.getParentColumns().size(); i++) {
            parentCols = parentCols + fk.getParentColumns().get(i);
            if (i < (fk.getParentColumns().size() - 1))
                parentCols = parentCols + ",";
        }
        for (int i = 0; i < fk.getParentConditions().size(); i++) {
            parentConds = parentConds + fk.getParentConditions().get(i).toString();
            if (i < (fk.getParentConditions().size() - 1))
                parentConds = parentConds + ",";
        }
        fke.setAttribute("childTable", fk.getChildTable());
        fke.setAttribute("childColumns", childCols);
        fke.setAttribute("childConditions", childConds);
        fke.setAttribute("parentTable", fk.getParentTable());
        fke.setAttribute("parentColumns", parentCols);
        fke.setAttribute("parentConditions", parentConds);
        return fke;
    }

    public void writeDDL(Element e) {
        xml.getElementsByTagName("DDLs").item(0).appendChild(e);
    }

    public void save() {
        try {
            TransformerFactory transformerFactory = TransformerFactory.newInstance();
            Transformer transformer = transformerFactory.newTransformer();
            DOMSource source = new DOMSource(xml);
            StreamResult result = new StreamResult(new File(docName));
            transformer.transform(source, result);
        } catch (Exception ex) {
            System.err.println("ERROR: " + ex.getMessage());
        }
    }

    public Transaction createSchemaUpdateTransaction() {
        if (usingDBSchema) {
            NodeList versions = xml.getElementsByTagName("version");
            int newestVersion = 0;
            try {
                newestVersion = Integer.valueOf(versions.item(versions.getLength() - 1).getFirstChild().getNodeValue());
            }  catch (Exception e) {
            }

            if (dbVersion < newestVersion) {
                try {

                    // add all the node after the dbVersion
                    DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
                    DocumentBuilder db = dbf.newDocumentBuilder();
                    Document changes = db.newDocument();
                    Node changeDDL = changes.createElement("DDLs");
                    changes.appendChild(changeDDL);
                    int i = 0;
                    for (i = 0; i < versions.getLength(); i++) {
                        Node node = versions.item(i);
                        if (node.getFirstChild().getNodeValue().equals(String.valueOf(dbVersion))) {
                            node = node.getNextSibling();
                            while (node != null) {
                                Node importedNode = changes.importNode(node, true);
                                changeDDL.appendChild(importedNode);
                                node = node.getNextSibling();
                            }
                            break;
                        }
                    }

                    NodeList changeVersions = changes.getElementsByTagName("version");
                    for (int j = 0; j < versions.getLength() - i; j++) {
                        if (j == (versions.getLength() - i - 2)) {
                            Document newChanges = db.newDocument();
                            Node newDdls = newChanges.createElement("DDLs");
                            NodeList changesDDLChildren = changes.getElementsByTagName("DDLs").item(0).getChildNodes();
                            Node changesDDLChild = changesDDLChildren.item(0);
                            while (changesDDLChild != changeVersions.item(j)) {
                                Node importedNode = newChanges.importNode(changesDDLChild, true);
                                newDdls.appendChild(importedNode);
                                changesDDLChild = changesDDLChild.getNextSibling();
                            }
                        }
                    }

                    // add updated DDL childnode to dbSchema DDL node
                    Node dbSchemaDDL = dbSchema.getFirstChild();
                    NodeList changeDDLElements = changes.getFirstChild().getChildNodes();
                    for (i = 0; i < changeDDLElements.getLength(); i++) {
                        Node importedNode = dbSchema.importNode(changeDDLElements.item(i), true);
                        dbSchemaDDL.appendChild(importedNode);
                    }

                    // convert xml document to xml string
                    StringWriter stw = new StringWriter();
                    Transformer serializer = TransformerFactory.newInstance().newTransformer();
                    serializer.transform(new DOMSource(dbSchema), new StreamResult(stw));

                    DBConnection.executeNonQuery("insert into dbversion (version,xmlschema) values ( " + newestVersion + ", '" + stw.toString() + "')");
                    return transactionFromXmlChanges(dbSchema.getFirstChild().getChildNodes());
                } catch (Exception e) {
                    System.err.println("ERROR: " + e.toString());
                }
            } else {
                return transactionFromXmlChanges(dbSchema.getFirstChild().getChildNodes());
            }
        } else {
            return transactionFromXmlChanges();
        }
        return null;
    }

    private Transaction transactionFromXmlChanges() {
        // Create transaction for all of DDLs childnodes
        return transactionFromXmlChanges(xml.getFirstChild().getChildNodes());
    }

    private Transaction transactionFromXmlChanges(NodeList elements) {
        Transaction t = new Transaction();
        for (int i = 0; i < elements.getLength(); i++) {
            Element element = (Element) elements.item(i);
            String name = element.getNodeName();
            String type = element.getAttribute("type");
            if (name.equals("table")) {
                if (type.equals("add")) {
                    t.add(new Command(this.addTableFromElement(element)));
                } else if (type.equals("rename")) {
                    t.add(new Command(new AlterTableRename(element.getAttribute("old"), element.getAttribute("new"))));
                } else if (type.equals("delete")) {
                    t.add(new Command(new DropTable(element.getAttribute("name"))));
                }
            } else if (name.equals("column")) {
                if (type.equals("add")) {
                    t.add(new Command(new AddColumn(element.getAttribute("table"),
                            element.getAttribute("name"), Domain.fromElement((Element) element.getFirstChild()))));
                } else if (type.equals("rename")) {
                    t.add(new Command(new AlterColumnRename(element.getAttribute("table"),
                            element.getAttribute("old"), element.getAttribute("old"))));
                } else if (type.equals("delete")) {
                    t.add(new Command(new DropColumn(element.getAttribute("table"), element.getAttribute("name"))));
                }
            } else if (name.equals("element")) {
                if (type.equals("add")) {
                    t.add(new Command(new AddElement(element.getAttribute("table"),
                            element.getAttribute("column"), element.getAttribute("value"))));
                } else if (type.equals("rename")) {
                    t.add(new Command(new AlterElementRename(element.getAttribute("table"),
                            element.getAttribute("column"), element.getAttribute("old"), element.getAttribute("new"))));
                } else if (type.equals("delete")) {
                    t.add(new Command(new DropElement(element.getAttribute("table"), element.getAttribute("column"), element.getAttribute("value"))));
                }
            } else if (name.equals("fk")) {
                if (type.equals("add")) {
                    t.add(new Command(addFKFromElement(element)));
                }
            }
        }
        return t;
    }

    private AddTable addTableFromElement(Element e) {
        AddTable at = new AddTable(e.getAttribute("name"));
        if (e.hasChildNodes()) {
            NodeList childNode = e.getChildNodes();
            for (int i = 0; i < childNode.getLength(); i++) {
                Element element = (Element) childNode.item(i);
                if (element.getAttribute("key").equals("true"))
                    at.addColumn(element.getAttribute("name"), Domain.fromElement(((Element) element.getFirstChild())), true);
                else
                    at.addColumn(element.getAttribute("name"), Domain.fromElement(((Element) element.getFirstChild())), false);
            }
        }
        return at;
    }

    public ForeignKey addFKFromElement(Element e) {
        ForeignKey fk = new ForeignKey(e.getAttribute("childTable"), e.getAttribute("parentTable"));
        List<String> childCols = new ArrayList<String>();
        String xmlChildCols = e.getAttribute("childColumns");
        List<String> parentCols = new ArrayList<String>();
        String xmlParentCols = e.getAttribute("parentColumns");
        while (xmlChildCols.contains(",")) {
            childCols.add(xmlChildCols.substring(0, xmlChildCols.indexOf(",")));
            xmlChildCols = xmlChildCols.substring(xmlChildCols.indexOf(",") + 2);
        }
        if (!xmlChildCols.equals("")) {
            childCols.add(xmlChildCols);
        }
        while (xmlParentCols.contains(",")) {
            parentCols.add(xmlParentCols.substring(0, xmlParentCols.indexOf(",")));
            xmlParentCols = xmlParentCols.substring(xmlParentCols.indexOf(",") + 2);
        }
        if (!xmlParentCols.equals(""))
            parentCols.add(xmlParentCols);
        fk.setChildColumns(childCols);
        fk.setParentColumns(parentCols);
        return fk;
    }
}
